﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SQLite;
using System.IO;
using Newtonsoft.Json.Linq;

namespace DataCenter
{
    public static class LocalDataManage
    {
        /// <summary>
        /// 本地应用数据库
        /// </summary>
        /// <param name="databasePath"></param>
        public static void scriptLocalDataBase(string databasePath) {


            // Extract directory path from the database path
            string directoryPath = Path.GetDirectoryName(databasePath);

            // Check if the directory exists, if not, create it
            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
            }

            // Connect to the database
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
            {
                connection.Open();

                // Create the missions table
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
    CREATE TABLE IF NOT EXISTS mscript (
        sid INTEGER PRIMARY KEY AUTOINCREMENT,
        script_id INTEGER,
        user_id INTEGER NOT NULL,
        script_name VARCHAR(128) DEFAULT NULL,
        script_type TEXT,
        introduce TEXT,
        description TEXT,
        mconfig TEXT,
        price INTEGER DEFAULT 0,
        approval_status INTEGER DEFAULT NULL,
        approval INTEGER DEFAULT NULL,
        favicon BLOB,
        date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
        date_update DATETIME DEFAULT CURRENT_TIMESTAMP
    );
";
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Database and table created successfully.");
            }
        }


        /// <summary>
        /// 本地应用数据库写入
        /// </summary>
        /// <param name="databasePath"></param>
        public static void scriptLocalWrite11(string databasePath,JObject data)
        {
            // Connect to the database
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
            {
                connection.Open();

                // Insert data into the mscript table
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                    INSERT INTO mscript (user_id, script_name, script_type, introduce, description, mconfig, price, approval_status, approval, favicon)
                    VALUES (@user_id, @script_name, @script_type, @introduce, @description, @mconfig, @price, @approval_status, @approval, @favicon);
                ";

                    //如果script_id存在就更新，不存在就插入
                    // Add parameters
                    command.Parameters.AddWithValue("@user_id", data.ContainsKey("user_id")?(int)data["user_id"]:0);
                    command.Parameters.AddWithValue("@script_name", data.ContainsKey("script_name")?(string)data["script_name"]:"");
                    //command.Parameters.AddWithValue("@script_type", "Type 1");
                    command.Parameters.AddWithValue("@script_type", data.ContainsKey("script_type") ? (string)data["script_type"] : "");
                    command.Parameters.AddWithValue("@introduce", data.ContainsKey("introduce") ? (string)data["introduce"]:"");
                    //command.Parameters.AddWithValue("@introduce", "Introduction 1");
                    //command.Parameters.AddWithValue("@description", "Description 1");
                    command.Parameters.AddWithValue("@description", data.ContainsKey("description") ? (string)data["description"] : "");
                    //command.Parameters.AddWithValue("@mconfig", "Config 1");
                    command.Parameters.AddWithValue("@mconfig", data.ContainsKey("mconfig") ? (string)data["mconfig"] : "");
                    //command.Parameters.AddWithValue("@price", 100);
                    command.Parameters.AddWithValue("@price", data.ContainsKey("price") ? (int)data["price"] : 0);
                    //command.Parameters.AddWithValue("@approval_status", 1);
                    command.Parameters.AddWithValue("@approval_status", data.ContainsKey("approval_status") ? (int)data["approval_status"] : 0);
                    //command.Parameters.AddWithValue("@approval", 1);
                    command.Parameters.AddWithValue("@approval", data.ContainsKey("approval") ? (int)data["approval"] : 0);
                    //command.Parameters.AddWithValue("@favicon", new byte[0]); // Replace with actual BLOB data
                    command.Parameters.AddWithValue("@favicon", data.ContainsKey("favicon") ? (byte[])data["favicon"] : new byte[0]);
                    // Execute the query
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Data inserted successfully into mscript table.");
            }
        }
        /// <summary>
        /// 本地应用数据库写入
        /// </summary>
        /// <param name="databasePath"></param>
        public static void scriptLocalWrite(string databasePath, JObject data)
        {
            // Connect to the database
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
            {
                connection.Open();

                // Check if script_id exists in the database
                int scriptId = data.ContainsKey("script_id") ? (int)data["script_id"] : 0;
                bool scriptExists = ScriptExists(connection, scriptId);

                // Decide whether to insert or update based on scriptExists
                if (scriptExists)
                {
                    // Update data in the mscript table
                    UpdateScript(connection, data);
                }
                else
                {
                    // Insert data into the mscript table
                    InsertScript(connection, data);
                }

                Console.WriteLine("操作成功完成。");
            }
        }

        // Helper method to check if script_id exists in the mscript table
        private static bool ScriptExists(SQLiteConnection connection, int scriptId)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = "SELECT COUNT(*) FROM mscript WHERE script_id = @script_id";
                command.Parameters.AddWithValue("@script_id", scriptId);

                return (long)command.ExecuteScalar() > 0;
            }
        }

        // Helper method to insert data into the mscript table
        private static void InsertScript(SQLiteConnection connection, JObject data)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = @"
                INSERT INTO mscript (script_id,user_id, script_name, script_type, introduce, description, mconfig, price, approval_status, approval, favicon)
                VALUES (@script_id, @user_id, @script_name, @script_type, @introduce, @description, @mconfig, @price, @approval_status, @approval, @favicon);
            ";

                // Add parameters
                command.Parameters.AddWithValue("@script_id", data.ContainsKey("script_id") ? (int)data["script_id"] : 0);
                command.Parameters.AddWithValue("@user_id", data.ContainsKey("user_id") ? (int)data["user_id"] : 0);
                command.Parameters.AddWithValue("@script_name", data.ContainsKey("script_name") ? (string)data["script_name"] : "");
                command.Parameters.AddWithValue("@script_type", data.ContainsKey("script_type") ? (string)data["script_type"] : "");
                command.Parameters.AddWithValue("@introduce", data.ContainsKey("introduce") ? (string)data["introduce"] : "");
                command.Parameters.AddWithValue("@description", data.ContainsKey("description") ? (string)data["description"] : "");
                command.Parameters.AddWithValue("@mconfig", data.ContainsKey("mconfig") ? (string)data["mconfig"] : "");
                command.Parameters.AddWithValue("@price", data.ContainsKey("price") ? (int)data["price"] : 0);
                command.Parameters.AddWithValue("@approval_status", data.ContainsKey("approval_status") ? (int)data["approval_status"] : 0);
                command.Parameters.AddWithValue("@approval", data.ContainsKey("approval") ? (int)data["approval"] : 0);
                command.Parameters.AddWithValue("@favicon", data.ContainsKey("favicon") ? (byte[])data["favicon"] : new byte[0]);

                // Execute the query
                command.ExecuteNonQuery();
            }
        }

        // Helper method to update data in the mscript table
        private static void UpdateScript(SQLiteConnection connection, JObject data)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = @"
                UPDATE mscript
                SET user_id = @user_id,
                    script_name = @script_name,
                    script_type = @script_type,
                    introduce = @introduce,
                    description = @description,
                    mconfig = @mconfig,
                    price = @price,
                    approval_status = @approval_status,
                    approval = @approval,
                    favicon = @favicon
                WHERE script_id = @script_id;
            ";

                // Add parameters
                command.Parameters.AddWithValue("@user_id", data.ContainsKey("user_id") ? (int)data["user_id"] : 0);
                command.Parameters.AddWithValue("@script_name", data.ContainsKey("script_name") ? (string)data["script_name"] : "");
                command.Parameters.AddWithValue("@script_type", data.ContainsKey("script_type") ? (string)data["script_type"] : "");
                command.Parameters.AddWithValue("@introduce", data.ContainsKey("introduce") ? (string)data["introduce"] : "");
                command.Parameters.AddWithValue("@description", data.ContainsKey("description") ? (string)data["description"] : "");
                command.Parameters.AddWithValue("@mconfig", data.ContainsKey("mconfig") ? (string)data["mconfig"] : "");
                command.Parameters.AddWithValue("@price", data.ContainsKey("price") ? (int)data["price"] : 0);
                command.Parameters.AddWithValue("@approval_status", data.ContainsKey("approval_status") ? (int)data["approval_status"] : 0);
                command.Parameters.AddWithValue("@approval", data.ContainsKey("approval") ? (int)data["approval"] : 0);
                command.Parameters.AddWithValue("@favicon", data.ContainsKey("favicon") ? (byte[])data["favicon"] : new byte[0]);
                command.Parameters.AddWithValue("@script_id", data.ContainsKey("script_id") ? (int)data["script_id"] : 0);

                // Execute the query
                command.ExecuteNonQuery();
            }
        }








        /// <summary>
        /// 本地任务数据库
        /// </summary>
        /// <param name="databasePath"></param>
        public static void missionLocalDataBase(string databasePath)
        {


            // Extract directory path from the database path
            string directoryPath = Path.GetDirectoryName(databasePath);

            // Check if the directory exists, if not, create it
            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
            }

            // Connect to the database
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
            {
                connection.Open();

                // Create the missions table
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
    CREATE TABLE IF NOT EXISTS missions (
        mid INTEGER PRIMARY KEY AUTOINCREMENT,
        script_id INTEGER NOT NULL,
        mconfig TEXT,
        date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
        date_update DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (script_id) REFERENCES mscript(script_id)
    );
";
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Database and table created successfully.");
            }
        }


        /// <summary>
        /// 本地应用数据库写入
        /// </summary>
        /// <param name="databasePath"></param>
        public static void missionLocalWrite(string databasePath)
        {
            // Connect to the database
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
            {
                connection.Open();

                // Insert data into the missions table
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                    INSERT INTO missions (script_id, mconfig)
                    VALUES (@script_id, @mconfig);
                ";

                    // Add parameters
                    command.Parameters.AddWithValue("@script_id", 1); // Replace with the actual script_id
                    command.Parameters.AddWithValue("@mconfig", "Mission Config 1");

                    // Execute the query
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Data inserted successfully into missions table.");
            }
        }

    }
}
